package com.techthinker.dreamBuilder.Dictionary;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.techthinker.dreamBuilder.Database.DatabaseHelper;
import com.techthinker.dreamBuilder.Define.Constant;
import com.techthinker.dreamBuilder.Entity.Bookmark;
import com.techthinker.dreamBuilder.Entity.Dictionary;
import com.techthinker.dreamBuilder.Entity.Word;

public class BookmarkDAL {
	// private static final String TAG = "BookmarkService";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;
	private static final String KEY_NAME = "name";
	private static final String KEY_DICTIONARY_ID = "dictionary_id";
	private static final String KEY_WORD_ID = "word_id";
	private static final String KEY_DATE = "date";
	private static final String KEY_ROWID = "bookmark_id";

	public BookmarkDAL(Context ctx) {
		this.mCtx = ctx;
	}

	public BookmarkDAL open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx, Constant.DB_NAME);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public boolean isClosed() {
		if (mDbHelper == null)
			return true;
		else
			return false;
	}

	// create a bookmark item
	public long createBookmark(Bookmark bookmark) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, bookmark.getName());
		initialValues.put(KEY_DICTIONARY_ID, bookmark.getDictionary()
				.getDictionary_id());
		initialValues.put(KEY_WORD_ID, bookmark.getWord().getWord_id());
		initialValues.put(KEY_DATE, bookmark.getDate().toString());
		return mDb.insert(Constant.TABLE_BOOKMARK, null, initialValues);
	}

	// update a bookmark item
	public boolean updateBookmark(Bookmark bookmark) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, bookmark.getName());
		args.put(KEY_DICTIONARY_ID, bookmark.getDictionary().getDictionary_id());
		args.put(KEY_WORD_ID, bookmark.getWord().getWord_id());
		args.put(KEY_DATE, bookmark.getDate().toString());
		return mDb.update(Constant.TABLE_BOOKMARK, args, KEY_ROWID + "="
				+ bookmark.getBookmark_id(), null) > 0;
	}

	// delete a bookmark item
	public boolean deleteBookmark(Bookmark bookmark) {
		return mDb.delete(Constant.TABLE_BOOKMARK,
				KEY_ROWID + "=" + bookmark.getBookmark_id(), null) > 0;
	}

	public boolean deleteBookmark(int bookmark_id) {
		return mDb.delete(Constant.TABLE_BOOKMARK, KEY_ROWID + "="
				+ bookmark_id, null) > 0;
	}

	// retrieve all bookmarks
	public List<Bookmark> fetchAllBookmarks() {
		Dictionary tempDic;
		Word tempWord;
		Cursor mCursor = mDb.query(Constant.TABLE_BOOKMARK,
				new String[] { KEY_ROWID, KEY_NAME, KEY_DICTIONARY_ID,
						KEY_WORD_ID, KEY_DATE }, null, null, null, null, null);

		// fetch the related dictionary
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		tempDic = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_DICTIONARY_ID)));
		dicDAL.close();
		// fetch the related word
		WordDAL wordDAL = new WordDAL(mCtx);
		wordDAL.open();
		tempWord = wordDAL.fetchWordById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_WORD_ID)),
				tempDic.getVersion().replace(' ', '_'));
		wordDAL.close();

		List<Bookmark> bookmarksList = new ArrayList<Bookmark>();
		while (mCursor.moveToNext()) {
			Bookmark bookmark = new Bookmark();
			bookmark.setBookmark_id(mCursor.getInt(mCursor
					.getColumnIndex(KEY_ROWID)));
			bookmark.setName(mCursor.getString(mCursor.getColumnIndex(KEY_NAME)));
			bookmark.setDictionary(tempDic);
			bookmark.setWord(tempWord);
			bookmark.setDate(Date.valueOf(mCursor.getString(mCursor
					.getColumnIndex(KEY_DATE))));
			bookmarksList.add(bookmark);
		}
		mCursor.close();
		return bookmarksList;
	}

	// retrieve all bookmarks to cursor
	public Cursor fetchAllBookmarksToCursor() {
		return mDb.query(Constant.TABLE_BOOKMARK, new String[] { KEY_ROWID,
				KEY_NAME, KEY_DICTIONARY_ID, KEY_WORD_ID, KEY_DATE }, null,
				null, null, null, null);
	}

	// retrieve a bookmark item by id
	public Bookmark fetchBookmarkById(long rowId) throws SQLException {
		Dictionary tempDic;
		Word tempWord;

		Cursor mCursor = mDb.query(true, Constant.TABLE_BOOKMARK,
				new String[] { KEY_ROWID, KEY_NAME, KEY_DICTIONARY_ID,
						KEY_WORD_ID, KEY_DATE }, KEY_ROWID + "=" + rowId, null,
				null, null, null, null);

		// fetch the related dictionary
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		tempDic = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_DICTIONARY_ID)));
		dicDAL.close();
		// fetch the related word
		WordDAL wordDAL = new WordDAL(mCtx);
		wordDAL.open();
		tempWord = wordDAL.fetchWordById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_WORD_ID)),
				tempDic.getVersion().replace(' ', '_'));
		wordDAL.close();

		if (mCursor.moveToFirst()) {
			Bookmark bookmark = new Bookmark();
			bookmark.setBookmark_id(mCursor.getInt(mCursor
					.getColumnIndex(KEY_ROWID)));
			bookmark.setName(mCursor.getString(mCursor.getColumnIndex(KEY_NAME)));
			bookmark.setDictionary(tempDic);
			bookmark.setWord(tempWord);
			bookmark.setDate(Date.valueOf(mCursor.getString(mCursor
					.getColumnIndex(KEY_DATE))));
			return bookmark;
		}
		mCursor.close();
		return null;

	}
}
